ask "Can't understand cells to chart. See 'Help'" with "Help"
if it = "Help" then
choose browse tool
click at 450,328
exit mouseUp
else
exit mouseUp
end if
end if
end if
end if
if firstNum > lastNum then
ask "You must enter cells in ascending order. See Help" with "Help"
if it = "Help" then
choose browse tool
click at 450, 328
exit mouseUp
else
exit mouseUp
end if
end if
clearScreen
choose rect tool
set lineSize to 1
set filled to true
set pattern to 1
drag from 29,40 to 476,302
--**get number of entries and entry width**--
if char 1 of firstCell <> char 1 of lastCell then
put ((Number of field lastCell - Number of field firstCell)/20) +1 into numEntries
else
put lastNum - firstNum +1 into numEntries
end if
put (430/numEntries) into entryWidth
put (430/numEntries)-10 into entryWidth1
--**find scale for bars**--
put field firstCell into maxData
get Number of field firstCell
put it into cellNum
if char 1 of firstCell <> char 1 of lastCell then
repeat with x = 1 to numEntries
put max(maxData, field (cellNum +(20*x))) into maxData
end repeat
else
repeat with x = 1 to numEntries
put max(maxData, field (cellNum + x)) into maxData
end repeat
end if
--**plot chart**--
choose rect tool
set pattern to 22
if char 1 of firstCell <> char 1 of lastCell then
repeat with x = 0 to (numEntries-1)
put trunc (40 + (x * entrywidth)) into start
drag from start, 290 - (field (cellNum + (x*20)) / maxData) *240 to trunc (start + entryWidth1), 290
end repeat
else
repeat with x = 0 to (numEntries-1)
put trunc (40 + (x * entrywidth)) into start
drag from start, trunc (290 - (field (cellNum + x) / maxData)*240) to trunc (start + entryWidth1), 290
end repeat
end if
repeat with x = 1 to 11
hide card button x
end repeat
show button "Clear Chart"
show button "Print Chart"
choose browse tool
end mouseUp
on clearScreen
choose select tool
drag from 29, 40 to 476, 302
domenu "Clear Picture"
end clearScreen
-- part 187 (button)
-- low flags: 00
-- high flags: 8004
-- rect: left=359 top=321 right=340 bottom=402
-- title width / last selected line: 0
-- icon id / first selected line: 0 / 0
-- text alignment: 1
-- font id: 3
-- text size: 9
-- style flags: 0
-- line height: 12
-- part name: Stats
----- HyperTalk script -----
on mouseUp
ask "What cells do you want to average?" with "A1:A10"
if it = "Cancel" then
exit mouseUp
end if
--** get cells **--
put length of it into howLong
if howLong = 5 then --**two 1-digit cells**
put char 1 to 2 of it into firstCell
put char 4 to 5 of it into lastCell
put char 2 of firstCell into firstNum
put char 2 of lastCell into lastNum
else
if howLong = 6 then --1-digit, 2-digit
put char 1 to 2 of it into firstCell
put char 4 to 6 of it into lastCell
put char 2 of firstCell into firstNum
put char 2 to 3 of lastCell into lastNum
else
if howLong = 7 then --two 2-digit cells
put char 1 to 3 of it into firstCell
put char 5 to 7 of it into lastCell
put char 2 to 3 of firstCell into firstNum
put char 2 to 3 of lastCell into lastNum
else
ask "Can't understand cells to average. See 'Help'" with "Help"
if it = "Help" then
choose browse tool
click at 450,328
exit mouseUp
else
exit mouseUp
end if
end if
end if
end if
if firstNum > lastNum then
ask "You must enter cells in ascending order. See Help" with "Help"
if it = "Help" then
choose browse tool
click at 450, 328
exit mouseUp
else
exit mouseUp
end if
end if
--** put cells into data field **--
if char 1 of firstCell <> char 1 of lastCell then
put ((Number of field lastCell - Number of field firstCell)/20) +1 into numEntries
put (Number of field lastCell - Number of field firstCell)/20 into gap
else
put 1 into gap
put lastNum - firstNum +1 into numEntries
end if
put Number of field firstCell into cellCount
repeat with c = 0 to (numEntries-1)
put field (cellCount + (c * gap)) into line (c+1) of field "data"
end repeat
--** find mean and standard deviation **--
put empty into sum
repeat with c = 1 to numEntries
add line c of field "data" to sum
end repeat
put sum/numEntries into mean
put empty into sum
repeat with c = 1 to numEntries
add ((line c of field "data" - mean) * (line c of field "data" - mean)) to sum
end repeat
put sqrt(sum/(numEntries-1)) into SDev
--** find median and quartiles **--
if numEntries/2 = round(numEntries/2) then
put average(line numEntries/2 of field "data", line (numEntries/2)+1 of field "data") into median
else
put line round(numEntries/2) of field "data" into median
end if
put average(line round(numEntries/4) of field "data", line round(numEntries/4)+1 of field "data") into loQuart
put average(line round(numEntries*(3/4)) of field "data", line round(numEntries*(3/4)+1) of field "data") into hiQuart
--** find mode **--
put empty into mode
put empty into oldCount
repeat with a = 1 to numEntries
get line a of field "data"
put it into datSearch
put 0 into count
repeat with b = 1 to numEntries
if datSearch = line b of field "data" then
put (count+1) into count
end if
end repeat
if count = oldCount then --search for multiple modes
if datSearch <> mode then
put "multiple" into mode
end if
end if
if count > oldCount then --
put count into oldCount
if oldCount > 1 then
put datSearch into mode
else --no mode
put "-" into mode
end if
end if
end repeat
--** output data **--
domenu "New Field"
set style of last card field to shadow
set name of last card field to "new"
set rect of card field "new" to 126,103,426,248
set textSize of card field "new" to 12
set textAlign of card field "new" to left
set textFont of card field "new" to chicago
put "Mean = "&& mean into line 2 of card field "new"
put " Standard Deviation = " && SDEV into line 3 of card field "new"
put "Median = " && median into line 4 of card field "new"
put " Upper Quartile = " && hiQuart into line 5 of card field "new"
put " Lower Quartile = " && loQuart into line 6 of card field "new"
put "Mode = " && mode into line 7 of card field "new"
choose browse tool
put empty into field "data"
end mouseUp
-- part 188 (button)
-- low flags: 00
-- high flags: 8004
-- rect: left=116 top=321 right=341 bottom=150
-- title width / last selected line: 0
-- icon id / first selected line: 0 / 0
-- text alignment: 1
-- font id: 3
-- text size: 9
-- style flags: 0
-- line height: 12
-- part name: NPV
----- HyperTalk script -----
on mouseUp
ask "Enter cell range of cash flows" with "A1:A10"
if it = "Cancel" then
exit mouseUp
end if
--** get cells **--
put length of it into howLong
if howLong = 5 then --**two 1-digit cells**
put char 1 to 2 of it into firstCell
put char 4 to 5 of it into lastCell
put char 2 of firstCell into firstNum
put char 2 of lastCell into lastNum
else
if howLong = 6 then --1-digit, 2-digit
put char 1 to 2 of it into firstCell
put char 4 to 6 of it into lastCell
put char 2 of firstCell into firstNum
put char 2 to 3 of lastCell into lastNum
else
if howLong = 7 then --two 2-digit cells
put char 1 to 3 of it into firstCell
put char 5 to 7 of it into lastCell
put char 2 to 3 of firstCell into firstNum
put char 2 to 3 of lastCell into lastNum
else
ask "Can't understand cash flowcells. See 'Help'" with "Help"
if it = "Help" then
choose browse tool
click at 450,328
exit mouseUp
else
exit mouseUp
end if
end if
end if
end if
if firstNum > lastNum then
ask "You must enter cells in ascending order. See Help" with "Help"
if it = "Help" then
choose browse tool
click at 450, 328
exit mouseUp
else
exit mouseUp
end if
end if
--** put cells in data field **--
if char 1 of firstCell <> char 1 of lastCell then
put ((Number of field lastCell - Number of field firstCell)/20) +1 into numEntries
put (Number of field lastCell - Number of field firstCell)/20 into gap
else
put 1 into gap
put lastNum - firstNum +1 into numEntries
end if
put Number of field firstCell into cellCount
repeat with c = 0 to (numEntries-1)
put field (cellCount + (c * gap)) into line (c+1) of field "data"
end repeat
--** get interest rate **--
ask "Enter Interest Rate as a decimal"
put it into r
--** determine Net Present Value **--
put empty into NPV
repeat with t = 2 to numEntries
get line t of field "data"
put 1 into x
repeat with c = 1 to (t-1)
put x * (1+r) into x
end repeat
add it/x to NPV
end repeat
add line 1 of field "data" to NPV --deduct initial cost
--** output data **--
put "Net Present Value = " && NPV into msg
put empty into field "data"
end mouseUp
-- part 189 (button)
-- low flags: 00
-- high flags: 8004
-- rect: left=156 top=321 right=340 bottom=190
-- title width / last selected line: 0
-- icon id / first selected line: 0 / 0
-- text alignment: 1
-- font id: 3
-- text size: 9
-- style flags: 0
-- line height: 12
-- part name: %╞
----- HyperTalk script -----
on mouseUp
ask "Enter first array of cells" with "A1:A10"
put length of it into howLong
if howLong = 5 then --**two 1-digit cells**
put char 1 to 2 of it into firstCell
put char 4 to 5 of it into lastCell
put char 2 of firstCell into firstNum
put char 2 of lastCell into lastNum
else
if howLong = 6 then --1-digit, 2-digit
put char 1 to 2 of it into firstCell
put char 4 to 6 of it into lastCell
put char 2 of firstCell into firstNum
put char 2 to 3 of lastCell into lastNum
else
if howLong = 7 then --two 2-digit cells
put char 1 to 3 of it into firstCell
put char 5 to 7 of it into lastCell
put char 2 to 3 of firstCell into firstNum
put char 2 to 3 of lastCell into lastNum
end if
end if
end if
--** put cells in data field **--
if char 1 of firstCell <> char 1 of lastCell then
put ((Number of field lastCell - Number of field firstCell)/20) +1 into numEntries
put (Number of field lastCell - Number of field firstCell)/20 into gap
else
put 1 into gap
put lastNum - firstNum +1 into numEntries
end if
put Number of field firstCell into cellCount
repeat with c = 0 to (numEntries-1)
put field (cellCount + (c * gap)) into item 1 of line (c+1) of field "data"
end repeat
ask "Enter second array of cells" with "B1:B10"
if howLong = 5 then --**two 1-digit cells**
put char 1 to 2 of it into firstCell
put char 4 to 5 of it into lastCell
put char 2 of firstCell into firstNum
put char 2 of lastCell into lastNum
else
if howLong = 6 then --1-digit, 2-digit
put char 1 to 2 of it into firstCell
put char 4 to 6 of it into lastCell
put char 2 of firstCell into firstNum
put char 2 to 3 of lastCell into lastNum
else
if howLong = 7 then --two 2-digit cells
put char 1 to 3 of it into firstCell
put char 5 to 7 of it into lastCell
put char 2 to 3 of firstCell into firstNum
put char 2 to 3 of lastCell into lastNum
end if
end if
end if
--** put cells in data field **--
if char 1 of firstCell <> char 1 of lastCell then
put ((Number of field lastCell - Number of field firstCell)/20) +1 into numEntries
put (Number of field lastCell - Number of field firstCell)/20 into gap
else
put 1 into gap
put lastNum - firstNum +1 into numEntries
end if
put Number of field firstCell into cellCount
repeat with c = 0 to (numEntries-1)
put field (cellCount + (c * gap)) into item 2 of line (c+1) of field "data"
end repeat
--** creat output field **--
domenu "New field"
set name of last card field to "Result"
set rect of card field "Result" to 189,33,253,312
set style of card field "Result" to rect
set textSize of card field "Result" to 10
--** calculate Percent Change **--
repeat with c = 1 to numEntries
put (item 1 of line c of field "data" - item 2 of line c of field "data")/item 1 of line c of field "data" into change
put change * 100 into change
--** output data **--
if change > 0 then
put "+" && change && "%" into line c of card field "Result"
else
if change < 0 then
put change && "%" into line c of card field "Result"
else
put "0%" into line c of card field "Result"
end if
end if
end repeat
put empty into field "data"
choose browse tool
end mouseUp
-- part 190 (button)
-- low flags: 00
-- high flags: 8004
-- rect: left=318 top=321 right=340 bottom=353
-- title width / last selected line: 0
-- icon id / first selected line: 0 / 0
-- text alignment: 1
-- font id: 3
-- text size: 9
-- style flags: 0
-- line height: 12
-- part name: Clear
----- HyperTalk script -----
on mouseUp
repeat with c = 1 to 140
put empty into field c
end repeat
end mouseUp
-- part 191 (button)
-- low flags: 00
-- high flags: 8004
-- rect: left=196 top=321 right=340 bottom=257
-- title width / last selected line: 0
-- icon id / first selected line: 0 / 0
-- text alignment: 1
-- font id: 3
-- text size: 9
-- style flags: 0
-- line height: 12
-- part name: Paste Table
----- HyperTalk script -----
on mouseUp
put empty into field tableData
show field tableData
choose browse tool
click at 66,35
domenu "Paste Text"
put number of lines of field tableData into rows
if rows > 20 then
answer "Too many rows of data" with OK
hide field tableData
exit mouseUp
end if
answer "How are data separated?" with "Spaces" or "Commas"
if it = "Spaces" then
repeat with x = 1 to rows
put 1 into y
repeat for (number of characters in line x of field "tableData")
if char y of line x of field "tableData" = " " then
put "," into char y of line x of field "tableData"
add 2 to y
else
add 1 to y
end if
end repeat
end repeat
end if
repeat with c = 1 to rows
put number of items in line c of field tableData into cols
if cols > 7 then
answer "Too many columns of data" with OK
hide field tableData
exit mouseUp
end if
repeat with d = 1 to cols
put item d of line c of field tableData into field (c + (20*(d-1)))
end repeat
end repeat
hide field tableData
choose browse tool
end mouseUp
-- part 192 (button)
-- low flags: 00
-- high flags: 8004
-- rect: left=260 top=321 right=340 bottom=316
-- title width / last selected line: 0
-- icon id / first selected line: 0 / 0
-- text alignment: 1
-- font id: 3
-- text size: 9
-- style flags: 0
-- line height: 12
-- part name: + Row/Col
----- HyperTalk script -----
on mouseUp
ask "What cells do you want to add?" with "A1:A10"
if it = "Cancel" then
exit mouseUp
end if
put length of it into howLong
if howLong = 5 then --**two 1-digit cells**
put char 1 to 2 of it into firstCell
put char 4 to 5 of it into lastCell
put char 2 of firstCell into firstNum
put char 2 of lastCell into lastNum
else
if howLong = 6 then --1-digit, 2-digit
put char 1 to 2 of it into firstCell
put char 4 to 6 of it into lastCell
put char 2 of firstCell into firstNum
put char 2 to 3 of lastCell into lastNum
else
if howLong = 7 then --two 2-digit cells
put char 1 to 3 of it into firstCell
put char 5 to 7 of it into lastCell
put char 2 to 3 of firstCell into firstNum
put char 2 to 3 of lastCell into lastNum
else
ask "Can't understand cells to add. See 'Help'" with "Help"
if it = "Help" then
choose browse tool
click at 450,328
exit mouseUp
else
exit mouseUp
end if
end if
end if
end if
if firstNum > lastNum then
ask "You must enter cells in ascending order. See Help" with "Help"
if it = "Help" then
choose browse tool
click at 450, 328
exit mouseUp
else
exit mouseUp
end if
end if
ask "Where do you want result?" with "New Field"
if it = "Cancel" then
exit mouseUp
end if
if it = "New Field" then
domenu "New Field"
set style of last field to rect
set name of last field to Total
set rect of field "Total" to 215, 140, 300, 152
set textSize of field "Total" to 9
set textAlign of field "Total" to center
put "Total" into totalCell
else
put it into totalCell
end if
put empty into sum
get Number of field firstCell
put it into cellNum
if char 1 of firstCell <> char 1 of lastCell then
repeat with x = 0 to ((Number of field lastCell - Number of field firstCell)/20)
add field (cellNum + (x*20)) to sum
end repeat
end if
if char 1 of firstCell = char 1 of lastCell then
repeat with x = 0 to (lastNum - firstNum)
add field (cellNum + x) to sum
end repeat
end if
put sum into field totalCell
choose browse tool
end mouseUp
-- part 193 (button)
-- low flags: 00
-- high flags: 8004
-- rect: left=6 top=321 right=340 bottom=29
-- title width / last selected line: 0
-- icon id / first selected line: 0 / 0
-- text alignment: 1
-- font id: 0
-- text size: 12
-- style flags: 0
-- line height: 16
-- part name: +
----- HyperTalk script -----
on mouseUp
ask " " with "Enter Cell"
if it <> "Enter Cell" then
put field it into x
end if
repeat while it <> "Enter Cell"
ask "+" with "Enter Cell"
if it = "Enter Cell" then exit repeat
put field it + x into x
end repeat
put x into msg
end mouseUp
-- part 194 (button)
-- low flags: 00
-- high flags: 8004
-- rect: left=35 top=321 right=340 bottom=58
-- title width / last selected line: 0
-- icon id / first selected line: 0 / 0
-- text alignment: 1
-- font id: 0
-- text size: 12
-- style flags: 256
-- line height: 16
-- part name: -
----- HyperTalk script -----
on mouseUp
ask " " with "Enter Cell"
put field it into cell1
ask "-" with "Enter Cell"
put cell1 - field it into msg
end mouseUp
-- part 195 (button)
-- low flags: 00
-- high flags: 8004
-- rect: left=62 top=321 right=340 bottom=85
-- title width / last selected line: 0
-- icon id / first selected line: 0 / 0
-- text alignment: 1
-- font id: 0
-- text size: 12
-- style flags: 0
-- line height: 16
-- part name: *
----- HyperTalk script -----
on mouseUp
ask " " with "Enter Cell"
if it <> "Enter Cell" then
put field it into x
end if
repeat while it <> "Enter Cell"
ask " " with "Enter Cell"
if it = "Enter Cell" then exit repeat
put field it * x into x
end repeat
put x into msg
end mouseUp
-- part 196 (button)
-- low flags: 00
-- high flags: 8004
-- rect: left=89 top=321 right=340 bottom=112
-- title width / last selected line: 0
-- icon id / first selected line: 0 / 0
-- text alignment: 1
-- font id: 0
-- text size: 12
-- style flags: 0
-- line height: 16
-- part name: ╓
----- HyperTalk script -----
on mouseUp
ask " " with "Enter Cell"
put field it into cell1
ask "├╖" with "Enter Cell"
put cell1 / field it into msg
end mouseUp
-- part 197 (button)
-- low flags: 00
-- high flags: 8004
-- rect: left=462 top=321 right=340 bottom=503
-- title width / last selected line: 0
-- icon id / first selected line: 0 / 0
-- text alignment: 1
-- font id: 3
-- text size: 9
-- style flags: 0
-- line height: 12
-- part name: Help
----- HyperTalk script -----
on mouseUp
show card field "Help"
end mouseUp
-- part 202 (button)
-- low flags: 80
-- high flags: 8004
-- rect: left=6 top=321 right=340 bottom=70
-- title width / last selected line: 0
-- icon id / first selected line: 0 / 0
-- text alignment: 1
-- font id: 3
-- text size: 9
-- style flags: 0
-- line height: 12
-- part name: Print Chart
----- HyperTalk script -----
on mouseUp
domenu "Print Card"
end mouseUp
-- part 203 (button)
-- low flags: 80
-- high flags: 8004
-- rect: left=224 top=321 right=340 bottom=288
-- title width / last selected line: 0
-- icon id / first selected line: 0 / 0
-- text alignment: 1
-- font id: 3
-- text size: 9
-- style flags: 0
-- line height: 12
-- part name: Clear Chart
----- HyperTalk script -----
on mouseUp
clearScreen
hide card button "Print Chart"
hide card button "Clear Chart"
repeat with x = 1 to 11
show card button x
end repeat
choose browse tool
end mouseUp
on clearScreen
choose select tool
drag from 29, 40 to 476, 302
domenu "Clear Picture"
end clearScreen
-- part contents for card part 145
----- text -----
             SPREAD
             by
              Maria Lima
This is a developmental copy of SPREAD,a spreadsheet designed as a single HyperCard card. It can be pasted into any stack, and accessed as needed. For protected stacks, its best to leave SPREAD as a separate stack and attach a button to access it. That way you can manipulate the scripts as needed to perform customized functions without changing the access level. If you are interested in the completed version of SPREAD, or have any suggestions or problems, please contact me.
The address (if you've lost it from the shareware notice) is:
Maria Lima, 5110 Frich Drive, Pgh., PA 15227.
You can easily perform any of the following functions with SPREAD:
Addition - to add consecutive cells in a column or row, press "+ Row/Col" and enter the first and last cell to be added, separated by a colon. (e.g. A1:A10). Sum can be placed into a cell or into a new card field.
To add nonconsecutive cells or cells of different rows and columns, use the "+" button.Enter cell numbers at prompt. When finished, hit return with "Enter Cell" still in window. Sum will appear in message box.
Subtraction - use "-" button. Enter number of first cell at prompt, return, then enter number of cell to be subtracted, return. Result will appear in message box.
Multiplication - use "x" button. Enter cell numbers to be multiplied at prompt. When finished, hit return with "Enter Cell" still in window. Product will appear in message box.
Division - use "├╖" button. Enter number of cell to be divided, return, then enter number of divisor. Result will appear in message box.
NPV - calcualtes the Net Present Value for a series of cash flows and places the result in the message box. Enter cells containing net cash flows for consecutive periods in the usual A1:A10 format. Negative flows should be designated with a "-" sign, but no "$" or commas should be included. The first cell is considered period 0, an immediate expenditure.
%Γêå - calculates the percentage change for a series of data. Data may be entered from rows or columns, but output will be displayed in a new field in columnar format.
Paste Table: enables you to paste in a table of figures from HyperCard fields or other documents into the spreadsheet cells. Copy the table, press the button, then specify if data are separated by commas or spaces. Data will be placed beginning in cell A1. Table can be no larger than 7 columns and/or 20 rows.
Clear: clears all spreadsheet cells
Stats: calculates the mean, median, mode, standard deviation and quartiles for specified data.
Chart: plots a bar chart of data in a row or column. Specify data cells in the usual A1:A10 format at prompt.
SPREAD is not a replacement for a full spreadsheet. If you have an Excel-size job
(i.e. large mass of data to enter, complex calculations, etc.) you would be better off accessing your regular spreadsheet program through a Hypercard script. But if you only need a few columns, SPREAD is for you.
